#include "mainwindow.h"
#include "ui_mainwindow.h"


QSqlTableModel *model;

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    if(true == createConnection())
    {
        qDebug()<<"创建成功";
    }
    else
    {
        qDebug()<<"创建失败";
        return;
    }
    model = new QSqlTableModel(this);
    model->setTable("student");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);//设定何时将更改更新到数据库？
    model->select(); //选取整个db表到model中
    //model->select("测试");

    //model->removeColumn(0); //不显示name属性列,如果这时添加记录，则该属性的值添加不上
    //ui->tableView->setHeaderData(0,Qt::Horizontal,"报修单号");


    ui->tableView->setModel(model);

    // ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);   //使其不可编辑

}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_pushButtonSave_clicked()
{
    //为何不直接用submitAll提交，而用
    model->database().transaction(); //启动事务操作
    if (model->submitAll())
    {
        model->database().commit(); //提交
    }
    else
    {
        model->database().rollback(); //回滚
        QMessageBox::information(this,"提示",QString("出错%1").arg(model->lastError().text()));
    }

}

void MainWindow::on_pushButtonRevert_clicked()
{
    model->revertAll();
}

void MainWindow::on_pushButtonShowAll_clicked()
{
    model->setFilter("");
    model->select();   //刷新显示整个表的内容
}

void MainWindow::on_pushButtonDelRow_clicked()
{
    int curRow = ui->tableView->currentIndex().row();
    qDebug("选中的是第%d行，请确认真的要删除吗？",curRow);


    int ok = QMessageBox::warning(this,"删除当前行","你确定删除当前行吗？",
                         QMessageBox::Yes,QMessageBox::No);
    if(ok == QMessageBox::No)
    {
        qDebug()<<"取消删除";
    }
    else
    {
        qDebug()<<"正在删除";
        ok = model->removeRow(curRow);
        if(ok)
        {
            qDebug()<<"删除成功";
            model->submitAll(); //将删除操作提交到数据库
            model->select();   //刷新model以便更新ui显示
            ui->tableView->selectRow(curRow);//自动选中下一行
        }
        else
        {
            qDebug()<<"删除失败";
        }
    }
}

void MainWindow::on_pushButtonAddRow_clicked()
{
    int rowNum = model->rowCount(); //获得表的行数
    model->insertRow(rowNum); //添加一行
    model->setData(model->index(rowNum,1),"");//至少要在非主键单元格中执行一次写入操作，否则存在空行，会提交失败
    if(model->submitAll())
    {
        qDebug()<<"添加行成功";
    }
    else
    {
        qDebug()<<"添加行失败:"<<model->lastError().text();
    }
}

void MainWindow::on_pushButtonSelectRow_clicked()
{
       model->selectRow(2);
}

void MainWindow::on_pushButtonShowColumns_clicked()
{

}

void MainWindow::on_pushButtonAbstractColumn_clicked()
{
    bool isOK;
    QString column_name_str = QInputDialog::getText(NULL, "Input Dialog",
                                                       "请输入指定的列名",
                                                       QLineEdit::Normal,
                                                       "name",
                                                       &isOK);
    if(!isOK) return;
    int rowCount = model->rowCount();
    QSqlRecord record;//QSqlRecord类，是某行的所有内容


    ui->textEdit->insertPlainText("您指定的列的内容是:");

    for(int row = 0; row<rowCount; row++ )
    {
        record = model->record(row);
        QString cell_str = record.value(column_name_str).toString();
        ui->textEdit->insertPlainText(cell_str + "\r\n");
    }


}

void MainWindow::on_pushButtonIsExist_clicked()
{
    bool isOK;
    QString text = QInputDialog::getText(NULL, "Input Dialog",
                                                       "请输入要查找的表名",
                                                       QLineEdit::Normal,
                                                       "student",
                                                       &isOK);
    if(! isOK) return;
    QSqlQuery query;
    //"select count(*) from sqlite_master where type='table' and name = 'teacher'"
    int ok = query.exec("select count(*) from sqlite_master where type='table' and name = '" + text + "'");
    if(!ok)
        qDebug()<<"query，查找指定的table名在数据库中的索引，测试失败:"<<query.lastError();
    else
    {
        query.first();//定位到query结果集的第一条记录处
        if(query.value(0).toInt() > 0)
            QMessageBox::information(this,"查找结果是","存在 " + text + " 这张表");
        else
            QMessageBox::information(this,"查找结果是","不存在 " + text + " 这张表");
    }
}

void MainWindow::on_pushButtonLishColName_clicked()
{
    bool isOK;
    QString text = QInputDialog::getText(NULL, "Input Dialog",
                                                       "您想列出哪张表的所有列名？",
                                                       QLineEdit::Normal,
                                                       "student",
                                                       &isOK);
    if(! isOK) return;

    QSqlQuery query;
    //PRAGMA table_info(student)//列出某表的所有字段的信息（包括列名和所有条目）
    int ok = query.exec("PRAGMA table_info(" +  text  +  ")");
    if(!ok)
        qDebug()<<"query，查询某表的详细信息失败:"<<query.lastError();
    else
    {
        QString ret;
        while(query.next())
        {
            qDebug()<<query.value(0).toString()<<query.value(1).toString();
            ret += QString("第%1列列名为:").arg(query.value(0).toInt()) + query.value(1).toString() + "\r\n";
        }
        QMessageBox::information(this,"所有的列及其列号如下：",ret);
    }
}

void MainWindow::on_pushButtonHideColumns_clicked()
{
    bool isOK;
    QString text = QInputDialog::getText(NULL, "Input Dialog",
                                                       "想隐藏第几列？",
                                                       QLineEdit::Normal,
                                                       "0",
                                                       &isOK);
    if(! isOK) return;
    bool ok;
    int row = text.toInt(&ok);
    if(ok)
        ui->tableView->hideColumn(row);
    else
        QMessageBox::information(this,"错误提示","您输入的不是数字，无法执行");
}

void MainWindow::on_pushButtonReadCell_clicked()
{
    bool isOK;
    QString row_text = QInputDialog::getText(NULL, "Input Dialog",
                                                       "请输入要查找的行号",
                                                       QLineEdit::Normal,
                                                       "0",
                                                       &isOK);
    if(! isOK) return;
    QString column_text = QInputDialog::getText(NULL, "Input Dialog",
                                                       "请输入要查找的列号",
                                                       QLineEdit::Normal,
                                                       "0",
                                                       &isOK);
    if(! isOK) return;
    QModelIndex index = model->index(row_text.toInt(),column_text.toInt());


    QString result_str = model->data(index).toString();
    QMessageBox::information(this,"结果是:",result_str);


}

void MainWindow::on_pushButtonGetColName_clicked()
{
    bool isOK;
    QString text = QInputDialog::getText(NULL, "Input Dialog",
                                                       "您想查看第几列的列名?",
                                                       QLineEdit::Normal,
                                                       "0",
                                                       &isOK);
    if(!isOK) return;
    QString Column_name_str = model->record().fieldName(text.toInt());
    QMessageBox::information(this,"结果:",QString("第%1列的列名是: ").arg(text.toInt()) + Column_name_str);

}

void MainWindow::on_pushButtonSortEnable_clicked()
{
    ui->tableView->setSortingEnabled(true);//排序使能
    ui->tableView->horizontalHeader()->sortIndicatorOrder();//水平头显示排序按钮
}

void MainWindow::on_pushButtonFindInforByDid_clicked()
{
    bool isOK;
    QString did_str = QInputDialog::getText(NULL, "Input Dialog",
                                                       "请输入did",
                                                       QLineEdit::Normal,
                                                       "C012",
                                                       &isOK);
    if(!isOK) return;

    model->setFilter(QString("did = '%1' ").arg(did_str));
    model->select();
    if(model->rowCount()<1)//未找到
    {
        QMessageBox::information(this,"结果:","未找到指定的did及其相关信息");
        return;
    }
    if(model->rowCount()>1)//找到了多条该did的信息
    {
        QMessageBox::information(this,"结果:",QString("该did相关信息不止一条，共有%1条").arg(model->rowCount()));
        return;
    }

    QSqlRecord record;//QSqlRecord类，是某行的所有内容

    QMessageBox::information(this,"该唯一的did相关信息已找到,对应行的name信息为",
                             model->record(0).value("name").toString());

}

void MainWindow::on_pushButtonFilter_clicked()
{
    bool isOK;
    QString key_str = QInputDialog::getText(NULL, "Input Dialog",
                                                       "请输入关键进行过滤（程序将在全表检索该关键字）",
                                                       QLineEdit::Normal,
                                                       "C012",
                                                       &isOK);
    if(!isOK) return;
    if(key_str == "")
    {
        model->setFilter( "" );
        model->select();
        return;

    }
    QString Filter_str;
    int column;
    int columnCount = model->columnCount();
    qDebug("列数为%d",columnCount);
    for(column=0; column < columnCount; column++)
    {
        qDebug("正在组建第%d列的过滤器",column);
        QString Column_name_str = model->record().fieldName(column);
        QString single_column_filter = QString(" %1 LIKE '%%2%' ").arg(Column_name_str).arg(key_str);// "name='lucy'"
        Filter_str += (column!=0?"OR":"") + single_column_filter;
    }
    qDebug()<<"全文过滤器为:"<<Filter_str;

    //model->setFilter( QString("did = '%1' ").arg(key_str) + QString(" OR name = '%1' ").arg(key_str));
    model->setFilter( Filter_str );
    model->select();
}
